package com.dxy.dao.impl;

import com.dxy.bean.Courier;
import com.dxy.bean.User;
import com.dxy.dao.BaseCourieDao;
import com.dxy.utils.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseCourieDaoImpl implements BaseCourieDao {
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size," + "COUNT(TO_DAYS(registeredTime)=TO_DAYS(NOW()) OR NULL) data1_day," + "COUNT(status=0 OR NULL) data2_size," + "COUNT(TO_DAYS(registeredTime)=TO_DAYS(NOW()) AND status=0 OR NULL) data2_day FROM courier";
    //用于查询数据库中的所有用户信息
    public static final String SQL_FIND_ALL = "SELECT * FROM courier";
    //用于分页查询数据库中的快递信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM courier LIMIT ?,?";
    //通过用户手机号查询用户所有用户
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM courier WHERE USERPHONE=?";
    //用户添加
    public static final String SQL_INSERT = "INSERT INTO courierR (USERNAME,USERPHONE,PASSWORD,ICCARD,REGISTEREDTIME,STATUS) VALUES(?,?,?,?,NOW(),0)";
    //用户修改
    public static final String SQL_UPDATE = "UPDATE courier SET USERNAME=?,USERPHONE=?,PASSWORD=?,ICCARD=? WHERE ID=?";
    //用户修改
    public static final String SQL_WX_UPDATE = "UPDATE courier SET USERNAME=?,USERPHONE=? ,ICCARD=?,PASSWORD=? WHERE USERPHONE=?";
    //用户的删除
    public static final String SQL_DELETE = "DELETE FROM courier WHERE ID=?";
    //用户的删除
    public static final String SQL_PHONE_DELETE = "DELETE FROM COURIER WHERE USERPHONE=?";
    //根据手机号查询用户是否存在于该表中
    public static final String SQL_FIND_USER_PHONE ="SELECT USERPHONE FROM COURIER WHERE USERPHONE=?";
    //根据手机号查询用户是否存在于该表中
    public static final String SQL_FIND_USER_NAME ="SELECT USERNAME FROM COURIER WHERE USERPHONE=?";
    /**
     * 用于查询数据库中的全部用户（总数+新增），待取件快递（总数+新增）
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1.  获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        //2.  预编译处理
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            //3.  填充参数

            //4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();

            //5. 获取执行结果
            if (resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);

            }

            //6. 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }



        return data;

    }

    /**
     * 用于查询所有用户
     *
     * @param limit      是否分页的标记，true表示分页。false表示查询所有快递
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<Courier> findByUserAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //1.  获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //3.    填充参数(可选)
        try {
            //2.  预编译处理
            if(limit) {
                preparedStatement= connection.prepareStatement(SQL_FIND_LIMIT);
                //3.    填充参数(可选)
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            }else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5. 获取执行结果
            while(resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String password = resultSet.getString("password");
                String icCard = resultSet.getString("icCard");
                String sendTheNumber = resultSet.getString("sendTheNumber");
                Timestamp registeredTime = resultSet.getTimestamp("registeredTime");
                Timestamp loginTime = resultSet.getTimestamp("loginTime");
                int status = resultSet.getInt("status");
                Courier courier = new Courier(id, username, userPhone, password,icCard,sendTheNumber, registeredTime, loginTime, status);
                data.add(courier);
            }



        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }


        return data;

    }

    /**
     * 根据用户手机号码
     *
     * @param userPhone 手机号码
     * @return 查询的快递信息列表
     */
    @Override
    public List<Courier> findByUserPhone(String userPhone) {
        ArrayList<Courier> data = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //   1.获取连接
            connection = DruidUtil.getConnection();
            //  2.预编译处理
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            //   3.填充参数
            preparedStatement.setString(1,userPhone);
            //  4.执行SQL
            resultSet = preparedStatement.executeQuery();
            //  5.获得执行结果
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String icCard = resultSet.getString("icCard");
                String sendTheNumber = resultSet.getString("sendTheNumber");
                Timestamp registeredTime = resultSet.getTimestamp("registeredTime");
                Timestamp loginTime = resultSet.getTimestamp("loginTime");
                int status = resultSet.getInt("status");
                Courier courier = new Courier(id,username,userPhone,password,icCard,sendTheNumber,registeredTime,loginTime,status);
                data.add(courier);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6. 释放资源
            DruidUtil.close(connection,preparedStatement,resultSet);
        }


        return data;
    }


    /**
     * 新增用户
     *
     * @param courier 新注册的用户
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(Courier courier) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;

        //  2.执行预编译处理
        try {
            //  1.获取数据库连接
            connection = DruidUtil.getConnection();
            //  2.执行预编译处理
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            //  3.填充参数

            preparedStatement.setString(1,courier.getUsername());
            preparedStatement.setString(2, courier.getUserPhone());
            preparedStatement.setString(3,courier.getPassword());
            preparedStatement.setString(4, courier.getIcCard());
            //  4.执行SQL并返回结果
            return preparedStatement.executeUpdate()>0?true:false;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,null);
        }







        //  6.释放资源

        return false;
    }



    /**
     * 用户的修改
     *
     * @param id      要修改的手机号userPhone
     * @param  NewCourier 修改快速员（name，userPhone,idCard,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(int id, Courier NewCourier) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //  1.获取数据库连接
            connection = DruidUtil.getConnection();
            //  2.执行预编译处理
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            //  3.填充参数
            preparedStatement.setString(1,NewCourier.getUsername());
            preparedStatement.setString(2,NewCourier.getUserPhone());
            preparedStatement.setString(3,NewCourier.getPassword());
            preparedStatement.setString(4,NewCourier.getIcCard());
            preparedStatement.setInt(5, id);
            //  4.执行SQL语句
            //  5. 返回结果
            return preparedStatement.executeUpdate()>0?true:false;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,null);
        }

        return false;

    }

    /**
     * 用户的修改
     *
     * @param userPhone 要修改的手机号userPhone
     * @param newUser   新增用户（name，userPhone,idCard,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean wxInfoUpdate(String userPhone, Courier newUser) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //  1.获取数据库连接
            connection = DruidUtil.getConnection();
            //  2.执行预编译处理
            preparedStatement = connection.prepareStatement(SQL_WX_UPDATE);
            //  3.填充参数
            preparedStatement.setString(1,newUser.getUsername());
            preparedStatement.setString(2,newUser.getUserPhone());
            preparedStatement.setString(3,newUser.getIcCard());
            preparedStatement.setString(4, newUser.getPassword());
            preparedStatement.setString(5,"userPhone");
            //  4.执行SQL语句
            //  5. 返回结果
            return preparedStatement.executeUpdate()>0?true:false;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,null);
        }

        return false;
    }

    /**
     * 根据id，删除单个快递信息
     *
     * @param id 要删除的快递id
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //  1。获取一个连接
            connection = DruidUtil.getConnection();
            //  2.执行预编译处理
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            //  3.填充参数
            preparedStatement.setInt(1,id);
            //  4.执行SQL语句并返回结果
            return preparedStatement.executeUpdate()>0?true:false;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //  6.释放资源
            DruidUtil.close(connection,preparedStatement,null);

        }



        return false;

    }

    /**
     * 根据手机号，删除用户信息
     *
     * @param userPhone 要删除的用户手机号
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean phoneDelete(String userPhone) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //  1。获取一个连接
            connection = DruidUtil.getConnection();
            //  2.执行预编译处理
            preparedStatement = connection.prepareStatement(SQL_PHONE_DELETE);
            //  3.填充参数
            preparedStatement.setString(1,userPhone);
            //  4.执行SQL语句并返回结果
            return preparedStatement.executeUpdate()>0?true:false;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //  6.释放资源
            DruidUtil.close(connection,preparedStatement,null);

        }
        return false;
    }

    /***
     * 根据手机号查询快递员表中的手机号
     * @param userPhone
     * @return
     */
    @Override
    public String findCouierPhone(String userPhone) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //   1.获取连接
            connection = DruidUtil.getConnection();
            //  2.预编译处理
            preparedStatement = connection.prepareStatement(SQL_FIND_USER_PHONE);
            //   3.填充参数
            preparedStatement.setString(1,userPhone);
            //  4.执行SQL
            resultSet = preparedStatement.executeQuery();
            //  5.获得执行结果
            while (resultSet.next()){
                String couierPhone = resultSet.getString("userPhone");

                return couierPhone;


            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6. 释放资源
            DruidUtil.close(connection,preparedStatement,resultSet);
        }


        return null;
    }
    /***
     * 根据手机号查询快递员表中的手机号
     * @param userPhone
     * @return
     */
    @Override
    public String findCouierUsername(String userPhone) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //   1.获取连接
            connection = DruidUtil.getConnection();
            //  2.预编译处理
            preparedStatement = connection.prepareStatement(SQL_FIND_USER_NAME);
            //   3.填充参数
            preparedStatement.setString(1,userPhone);
            //  4.执行SQL
            resultSet = preparedStatement.executeQuery();
            //  5.获得执行结果
            while (resultSet.next()){
                String username = resultSet.getString("username");

                return username;


            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6. 释放资源
            DruidUtil.close(connection,preparedStatement,resultSet);
        }


        return null;
    }

}
